﻿using Dapper;
using ExcelImport;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace ExcelImport.Oracle
{
    public class OracleDatabase : IDatabase
    {
        private OracleConnection _conn;

        public OracleDatabase(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString)) throw new ArgumentException(connectionString);
            _conn = new OracleConnection(connectionString);
        }

        public void BlukCopy<T>(DataTable dt, ImportTable importTable) where T : class, new()
        {
            #region 无法使用特殊列
            //dt.Rows.RemoveAt(0);

            //var importProperties = importTable.ImportProperties.Where(p => !p.IsTemp).ToList();

            //OracleParameter[] oracleParameters = new OracleParameter[importProperties.Count];
            //List<string> propertyNames = new List<string>();
            //Dictionary<string, object[]> propertyValues = new Dictionary<string, object[]>(importProperties.Count);

            //for (int i = 0; i < importProperties.Count; i++)
            //{
            //    var importProperty = importProperties[i];

            //    var propertyName = importProperty.PropertyName;

            //    object[] arr = new object[dt.Rows.Count];

            //    for (int j = 0; j < dt.Rows.Count; j++)
            //    {
            //        object value = dt.Rows[j].GetValue(propertyName, importProperty.DataType);

            //        arr[j] = value;
            //    }

            //    oracleParameters[i] = new OracleParameter(propertyName, GetOracleDbType(importProperty.DataType), arr, ParameterDirection.Input);

            //    propertyNames.Add(propertyName);
            //}

            //string properties = string.Join(",", propertyNames);
            //string values = string.Join(",", propertyNames.Select(p => $":{p}"));
            //string sql = $"INSERT INTO {importTable.Name}({properties}) VALUES ({values})";

            //OracleCommand cmd = new OracleCommand(sql, _conn);
            //cmd.CommandTimeout = 600;
            //cmd.ArrayBindCount = dt.Rows.Count;

            //cmd.Parameters.AddRange(oracleParameters);

            //if (_conn.State != ConnectionState.Open)
            //{
            //    _conn.Open();
            //}

            //cmd.ExecuteNonQuery();
            #endregion

            dt.Rows.RemoveAt(0);

            var importProperties = importTable.ImportProperties.Where(p => !p.IsTemp);

            var propertyNames = importProperties.Select(p => p.PropertyName);

            string properties = string.Join(",", propertyNames);
            string values = string.Join(",", propertyNames.Select(p => $":{p}"));
            string sql = $"INSERT INTO {importTable.Name}({properties}) VALUES ({values})";

            List<T> list = dt.ToList<T>(importTable);

            _conn.Execute(sql, list);
        }


        public OracleDbType GetOracleDbType(Type type)
        {
            if (type == typeof(DateTime))
            {
                return OracleDbType.Date;
            }
            else if (type == typeof(int) || type == typeof(int?))
            {
                return OracleDbType.Int32;
            }
            else if (type == typeof(float) || type == typeof(float?))
            {
                return OracleDbType.Single;
            }
            else if (type == typeof(double) || type == typeof(double?))
            {
                return OracleDbType.Double;
            }
            else if (type == typeof(decimal) || type == typeof(decimal?))
            {
                return OracleDbType.Decimal;
            }
            else if (type == typeof(string))
            {
                return OracleDbType.Varchar2;
            }
            throw new NotSupportedException($"暂不支持的数据类型{type.Name},可继承IDatabase自行实现");
        }

        public void Dispose()
        {
            this._conn.Dispose();
        }
    }
}
